package org.huel.backend.mapper;

import org.apache.ibatis.annotations.*;
import org.huel.backend.domain.dto.StudentScoreDTO;
import org.huel.backend.domain.entity.CourseAttendance;

import java.util.List;

@Mapper
public interface CourseAttendanceMapper {

    @Select("SELECT * FROM course_attendance")
    List<CourseAttendance> findAllCourses();

    @Select("SELECT * FROM course_attendance WHERE course_name = #{courseName}")
    CourseAttendance findByCourse(String courseName);

    @Select({
            "SELECT student_name, student_id, class_id, ",
            "SUM(score) as total_score, ",
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as attendance_count, ",
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absent_count, ",
            "#{courseName} as course_name ",
            "FROM attendance ",
            "WHERE course_name = #{courseName} ",
            "GROUP BY student_name, student_id, class_id ",
            "ORDER BY total_score DESC ",
            "LIMIT #{limit}"
    })
    @Results({
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "totalScore", column = "total_score"),
            @Result(property = "attendanceCount", column = "attendance_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "courseName", column = "course_name")
    })
    List<StudentScoreDTO> findTopStudentsByCourse(@Param("courseName") String courseName, @Param("limit") int limit);
    /**
     * 获取所有课程出勤记录
     * @return 课程出勤记录列表
     */
    @Select("SELECT * FROM course_attendance ORDER BY course_name")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "presentCount", column = "present_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<CourseAttendance> selectAllCourseAttendance();

    /**
     * 获取出勤人数最高的前N门课程
     * @param limit 限制返回的记录数
     * @return 出勤人数最高的课程列表
     */
    @Select("SELECT * FROM course_attendance ORDER BY present_count DESC LIMIT #{limit}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "presentCount", column = "present_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<CourseAttendance> selectTopPresentCourses(@Param("limit") int limit);

    /**
     * 获取缺勤人数最高的前N门课程
     * @param limit 限制返回的记录数
     * @return 缺勤人数最高的课程列表
     */
    @Select("SELECT * FROM course_attendance ORDER BY absent_count DESC LIMIT #{limit}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "presentCount", column = "present_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<CourseAttendance> selectTopAbsentCourses(@Param("limit") int limit);

    /**
     * 根据课程名称查询课程出勤情况
     * @param courseName 课程名称
     * @return 课程出勤记录
     */
    @Select("SELECT * FROM course_attendance WHERE course_name = #{courseName}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "presentCount", column = "present_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    CourseAttendance selectByCourseName(@Param("courseName") String courseName);

    /**
     * 插入新的课程出勤记录
     * @param courseAttendance 课程出勤实体
     * @return 影响的行数
     */
    @Insert("INSERT INTO course_attendance (course_name, present_count, absent_count) VALUES (#{courseName}, #{presentCount}, #{absentCount})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertCourseAttendance(CourseAttendance courseAttendance);

    /**
     * 更新课程出勤记录
     * @param courseAttendance 课程出勤实体
     * @return 影响的行数
     */
    @Update("UPDATE course_attendance SET present_count = #{presentCount}, absent_count = #{absentCount} WHERE id = #{id}")
    int updateCourseAttendance(CourseAttendance courseAttendance);
}